<?php

// 引入自动加载文件
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\DataType;

// 连接到MySQL数据库（请修改这些参数以适应你的数据库）
$servername = "localhost";
$username = "root";
$password = "159456";
$dbname = "excledemo";

try {
    $pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("数据库连接失败: " . $e->getMessage());
}

// 读取Excel文件（请修改文件名和路径）
$excelFileName = 'C:\Users\Administrator\Desktop\1.快手老店售后\1.19-2.05售后豆之芽(1).xlsx';
$spreadsheet = IOFactory::load($excelFileName);
$worksheet = $spreadsheet->getActiveSheet();

// 获取第一行作为表头，用于创建MySQL表结构
$headers = [];
foreach ($worksheet->getRowIterator(1) as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);
    foreach ($cellIterator as $cell) {
        $headers[] = $cell->getValue();
    }
    break;
}

// 构建创建表的SQL语句
$tableName = "order_refund";
$createTableSql = "CREATE TABLE IF NOT EXISTS $tableName (";
foreach ($headers as $header) {
    $createTableSql .= "`$header` VARCHAR(255), ";
}
$createTableSql = rtrim($createTableSql, ', ') . ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

// 执行创建表的SQL语句
$pdo->exec($createTableSql);

// 插入数据到MySQL表
$rowIterator = $worksheet->getRowIterator();
// 跳过表头行（第一行）
next($rowIterator);
foreach ($rowIterator as $row) {
    $insertSql = "INSERT INTO $tableName (";
    $valuesSql = " VALUES (";
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);
    $i = 0;
    foreach ($cellIterator as $cell) {
        $value = $cell->getValue();
        if ($i > 0) {
            $insertSql .= ", ";
            $valuesSql .= ", ";
        }
        $insertSql .= "`$headers[$i]`";
        if ($value === null) {
            $valuesSql .= "NULL";
        } else {
            $valuesSql .= "'" . $pdo->quote($value) . "'";
        }
        $i++;
    }
    $insertSql .= ") ";
    $valuesSql .= ")";
    $insertQuery = $insertSql . $valuesSql;
    $pdo->exec($insertQuery);
}

echo "数据导入成功！";